drop table Uzytkownik cascade constraints;
create table Uzytkownik(
	login varchar(255) constraint c_ulogin primary key,
	haslo varchar(255),
	uprawnienia varchar(40) default 'uzytkownik' constraint cu_up check(uprawnienia in ('admin', 'uzytkownik'))
	);
	
	
	
	
drop sequence seq_Stanowisko;
create sequence seq_Stanowisko;
drop table Stanowisko cascade constraints;
create table Stanowisko(
	id number(10) constraint s_id primary key,
	nazwa varchar(100)
	);
insert into Stanowisko(id, nazwa) values(seq_Stanowisko.nextval, 'Aktor');
insert into Stanowisko(id, nazwa) values(seq_Stanowisko.nextval, 'Re&#380;yseria');
insert into Stanowisko(id, nazwa) values(seq_Stanowisko.nextval, 'Wsp&#243;&#322;praca re&#380;yserska');
insert into Stanowisko(id, nazwa) values(seq_Stanowisko.nextval, 'Scenariusz');
insert into Stanowisko(id, nazwa) values(seq_Stanowisko.nextval, 'Dialogi');
insert into Stanowisko(id, nazwa) values(seq_Stanowisko.nextval, 'Zdj&#281;cia');
insert into Stanowisko(id, nazwa) values(seq_Stanowisko.nextval, 'Operator kamery');
insert into Stanowisko(id, nazwa) values(seq_Stanowisko.nextval, 'Asystent operatora obrazu');
insert into Stanowisko(id, nazwa) values(seq_Stanowisko.nextval, 'O&#347;wietlenie');
insert into Stanowisko(id, nazwa) values(seq_Stanowisko.nextval, 'Obs&#322;uga agregatu');
insert into Stanowisko(id, nazwa) values(seq_Stanowisko.nextval, 'W&#243;zkarz');
insert into Stanowisko(id, nazwa) values(seq_Stanowisko.nextval, 'Scenografia');
insert into Stanowisko(id, nazwa) values(seq_Stanowisko.nextval, 'Wsp&#243;&#322;praca scenograficzna');
insert into Stanowisko(id, nazwa) values(seq_Stanowisko.nextval, 'Dekoracja wn&#281;trz');
insert into Stanowisko(id, nazwa) values(seq_Stanowisko.nextval, 'Wsp&#243;&#322;praca dekoratorska');
insert into Stanowisko(id, nazwa) values(seq_Stanowisko.nextval, 'Kierownictwo budowy dekoracji');
insert into Stanowisko(id, nazwa) values(seq_Stanowisko.nextval, 'Wsp&#243;&#322;praca przy budowie dekoracji');
insert into Stanowisko(id, nazwa) values(seq_Stanowisko.nextval, 'Rekwizyty');
insert into Stanowisko(id, nazwa) values(seq_Stanowisko.nextval, 'Kostiumy');
insert into Stanowisko(id, nazwa) values(seq_Stanowisko.nextval, 'Wsp&#243;&#322;praca kostiumograficzna');
insert into Stanowisko(id, nazwa) values(seq_Stanowisko.nextval, 'Militaria');
insert into Stanowisko(id, nazwa) values(seq_Stanowisko.nextval, 'Muzyka');
insert into Stanowisko(id, nazwa) values(seq_Stanowisko.nextval, 'Aran&#380;acja (orkiestracja)');
insert into Stanowisko(id, nazwa) values(seq_Stanowisko.nextval, 'Wykonanie muzyki');
insert into Stanowisko(id, nazwa) values(seq_Stanowisko.nextval, 'Dyrygent');
insert into Stanowisko(id, nazwa) values(seq_Stanowisko.nextval, 'Konsultacja muzyczna');
insert into Stanowisko(id, nazwa) values(seq_Stanowisko.nextval, 'Realizacja nagra&#324;');
insert into Stanowisko(id, nazwa) values(seq_Stanowisko.nextval, 'Monta&#380; muzyki');
insert into Stanowisko(id, nazwa) values(seq_Stanowisko.nextval, 'D&#378;wi&#281;k');
insert into Stanowisko(id, nazwa) values(seq_Stanowisko.nextval, 'Asystent operatora d&#378;wi&#281;ku');
insert into Stanowisko(id, nazwa) values(seq_Stanowisko.nextval, 'Wsp&#243;&#322;praca d&#378;wi&#281;kowa');
insert into Stanowisko(id, nazwa) values(seq_Stanowisko.nextval, 'Imitator d&#378;wi&#281;ku');
insert into Stanowisko(id, nazwa) values(seq_Stanowisko.nextval, 'Ud&#378;wi&#281;kowienie');
insert into Stanowisko(id, nazwa) values(seq_Stanowisko.nextval, 'Monta&#380;');
insert into Stanowisko(id, nazwa) values(seq_Stanowisko.nextval, 'Wsp&#243;&#322;praca monta&#380;owa');
insert into Stanowisko(id, nazwa) values(seq_Stanowisko.nextval, 'Charakteryzacja');
insert into Stanowisko(id, nazwa) values(seq_Stanowisko.nextval, 'Wsp&#243;&#322;praca charakteryzatorska');
insert into Stanowisko(id, nazwa) values(seq_Stanowisko.nextval, 'Konsultacja');
insert into Stanowisko(id, nazwa) values(seq_Stanowisko.nextval, 'Ewolucje kaskaderskie');
insert into Stanowisko(id, nazwa) values(seq_Stanowisko.nextval, 'Efekty pirotechniczne');
insert into Stanowisko(id, nazwa) values(seq_Stanowisko.nextval, 'Fotosy');
insert into Stanowisko(id, nazwa) values(seq_Stanowisko.nextval, 'Korekta barwna');
insert into Stanowisko(id, nazwa) values(seq_Stanowisko.nextval, 'Opracowanie graficzne');
insert into Stanowisko(id, nazwa) values(seq_Stanowisko.nextval, 'Kierownictwo produkcji');
insert into Stanowisko(id, nazwa) values(seq_Stanowisko.nextval, 'Wsp&#243;&#322;praca produkcyjna');
insert into Stanowisko(id, nazwa) values(seq_Stanowisko.nextval, 'Kierownictwo planu');
insert into Stanowisko(id, nazwa) values(seq_Stanowisko.nextval, 'Dy&#380;urny planu');
insert into Stanowisko(id, nazwa) values(seq_Stanowisko.nextval, 'Sekretariat planu');
insert into Stanowisko(id, nazwa) values(seq_Stanowisko.nextval, 'Administracja');
insert into Stanowisko(id, nazwa) values(seq_Stanowisko.nextval, 'Produkcja wykonawcza');
insert into Stanowisko(id, nazwa) values(seq_Stanowisko.nextval, 'Produkcja');
insert into Stanowisko(id, nazwa) values(seq_Stanowisko.nextval, 'Laboratorium');
insert into Stanowisko(id, nazwa) values(seq_Stanowisko.nextval, 'Obsada aktorska');




drop sequence seq_Film;
create sequence seq_Film;
drop table Film cascade constraints;
create table Film(
	id number(10) constraint c_fid primary key,
	tytul varchar(255),
	rok_produkcji number(4),
	punkty_suma_cache number(10) default 0,
	punkty_ilosc_cache number(10) default 0
	);
	
	
	
	
drop sequence seq_Recenzja;
create sequence seq_Recenzja;
drop table Recenzja cascade constraints;
create table Recenzja(
	film_id number(10) constraint fk_r_film references Film(id),	-- ma
	uzytkownik_login varchar(255) constraint fk_r_uzytkownik references Uzytkownik(login), -- napisal
	--
	id number(10) constraint c_rid primary key,
	tresc clob,
	punkty_suma_cache number(10) default 0,
	punkty_ilosc_cache number(10) default 0,
	napisana date default sysdate
	);

	
	
	
	
drop sequence seq_Czlowiek;
create sequence seq_Czlowiek;
drop table Czlowiek cascade constraints;
create table Czlowiek(
	id number(10) constraint c_c_id primary key,
	imie varchar(255),
	nazwisko varchar(255),
	data_urodzenia date,
	data_smierci date default NULL,
	bio clob
	);




drop sequence seq_Obsadzil;
create sequence seq_Obsadzil;
drop table Obsadzil cascade constraints;
create table Obsadzil(
	id number(10) constraint c_o_id primary key,
	film_id number(10) constraint fk_o_film references Film(id) on delete cascade,
	czlowiek_id number(10) constraint fk_o_czlowiek references Czlowiek(id) on delete cascade,
	stanowisko_id number(10) constraint fk_r_stanowisko references Stanowisko(id) on delete cascade	-- jako
	);




drop table OcenilFilm cascade constraints;
create table OcenilFilm(
	uzytkownik_login varchar(255) constraint fk_of_uzytkownik references Uzytkownik(login) on delete cascade,
	film_id number(10) constraint fk_of_film references Film(id) on delete cascade,
	na number(3) constraint c_of_na check(na>=1 and na<=100), -- %
	--
	constraint pk_of primary key(uzytkownik_login, film_id)
	);

create or replace trigger trig_OcenilFilm_1 before insert on OcenilFilm for each row
begin
	delete from OcenilFilm where uzytkownik_login = :new.uzytkownik_login and film_id = :new.film_id;
	update Film set punkty_suma_cache=punkty_suma_cache+:new.na, punkty_ilosc_cache=punkty_ilosc_cache+1 where id = :new.film_id;
end;
/
show errors;

create or replace trigger trig_OcenilFilm_2 after delete on OcenilFilm for each row
begin
	update Film set punkty_suma_cache=punkty_suma_cache-:old.na, punkty_ilosc_cache=punkty_ilosc_cache-1 where id = :old.film_id;
end;
/
show errors;




drop table OcenilRecenzje cascade constraints;
create table OcenilRecenzje(
	uzytkownik_login varchar(255) constraint fk_or_uzytkownik references Uzytkownik(login) on delete cascade,
	recenzja_id number(10) constraint fk_or_recenzja references Recenzja(id) on delete cascade,
	na number(3) constraint c_or_na check(na>=1 and na<=100), -- %
	--
	constraint pk_or primary key(uzytkownik_login, recenzja_id)
	);

create or replace trigger trig_OcenilRecenzje_1 before insert on OcenilRecenzje for each row
begin
	delete from OcenilRecenzje where uzytkownik_login = :new.uzytkownik_login and recenzja_id = :new.recenzja_id;
	update Recenzja set punkty_suma_cache=punkty_suma_cache+:new.na, punkty_ilosc_cache=punkty_ilosc_cache+1 where id = :new.recenzja_id;
end;
/
show errors;

create or replace trigger trig_OcenilRecenzje_2 after delete on OcenilRecenzje for each row
begin
	update Recenzja set punkty_suma_cache=punkty_suma_cache-:old.na, punkty_ilosc_cache=punkty_ilosc_cache-1 where id = :old.recenzja_id;
end;
/
show errors;




-- http://www.adp-gmbh.ch/ora/sql/user_def_agg.html
create or replace type agg_t as object (
	str_agg varchar2(4000),
	static function ODCIAggregateInitialize(sctx  in out agg_t) return number,
	member function ODCIAggregateIterate   (self  in out agg_t, value in varchar2) return number,
	member function ODCIAggregateTerminate (self in agg_t, return_value out varchar2, flags in number)return number,
	member function ODCIAggregateMerge(self in out agg_t, ctx2 in agg_t    ) return number
);
/

create or replace type body agg_t is 

    static function ODCIAggregateInitialize(sctx in out agg_t)
        return number is 
    begin
        sctx := agg_t(null);
        return ODCIConst.Success;
    end;

    member function ODCIAggregateIterate(
      self in out agg_t, value in varchar2) 
        return number is
    begin
    	if str_agg is null then
    		str_agg:=value;
    	else
        str_agg := str_agg || ', ' || value;
      end if;
        return ODCIConst.Success;
    end;

    member function ODCIAggregateTerminate(self in agg_t, 
        return_value out varchar2, flags in number) return number is
    begin
        return_value := str_agg;
        return ODCIConst.Success;
    end;

    member function ODCIAggregateMerge(self in out agg_t, 
        ctx2 in agg_t) return number is
    begin
        str_agg := str_agg || ctx2.str_agg;
        return ODCIConst.Success;
    end;
end;
/
show errors;

create or replace function agg_concat (input varchar2) return varchar2
	parallel_enable aggregate using agg_t;
/
show errors;





create or replace view obsada as
select
	czlowiek_id, 
	imie, 
	nazwisko,
	agg_concat(stanowisko) stanowisko,
	film_id
from
	(
		select
			czlowiek_id,
			obsadzil.id id,
			stanowisko.nazwa stanowisko,
			obsadzil.film_id
		from
			obsadzil, stanowisko
		where
			stanowisko.id = obsadzil.stanowisko_id
	),
	czlowiek
where
	czlowiek.id = czlowiek_id
group by
	czlowiek_id, film_id, imie, nazwisko;



-----

/*
insert into Uzytkownik values('A', '');
insert into Uzytkownik values('B', '');
insert into Film(id, tytul, rok_produkcji) values(seq_Film.nextval, 'Film1', 2004);
insert into Film(id, tytul, rok_produkcji) values(seq_Film.nextval, 'Film2', 2005);
insert into Film(id, tytul, rok_produkcji) values(seq_Film.nextval, 'Film3', 2006);
insert into OcenilFilm values('A', 1, 1);
insert into OcenilFilm values('A', 2, 5);
insert into OcenilFilm values('B', 1, 10);
insert into OcenilFilm values('B', 1, 2);
delete from Uzytkownik where login='B';
insert into Czlowiek(id, imie, nazwisko) values(seq_Czlowiek.nextval, 'jan', 'kowalski');
insert into Czlowiek(id, imie, nazwisko) values(seq_Czlowiek.nextval, 'adam', 'nowak');
insert into Obsadzil values(seq_Obsadzil.nextval, 1, 1, 2);
insert into Obsadzil values(seq_Obsadzil.nextval, 1, 2, 4);
insert into Obsadzil values(seq_Obsadzil.nextval, 1, 2, 3);
insert into Obsadzil values(seq_Obsadzil.nextval, 2, 2, 4);

select * from obsada where film_id = 1;
*/

